package com.xcx.service.user;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.xcx.mapper.DataBoardMapper;
import com.xcx.utils.SQLUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Map;

@Slf4j
@Service
public class DataBoardService {

    @Autowired
    private SQLUtils sqlUtils;

    @Autowired
    private DataBoardMapper dataBoardMapper;

    public JSONObject overview(Map<String, Object> map) {
        int storeLevel = Integer.parseInt(map.get("storeLevel").toString());
        String query = "";
        switch (storeLevel) {
            case 1:
                String userId = map.get("userId").toString();
                DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                String formatDay = LocalDate.now().format(formatter);
                return dataBoardMapper.queryEmpData(userId, formatDay);
            case 2:
                String sql2 = " with dtxs as (select subbh,sl,jshj,hedgehh,posguid,xsr,ml,yhkh,lsh,ph1_xq,hh from subfhd where kdrq=trunc(sysdate) and nvl(bz,' ')<>'会员礼品成本调整单' and nvl(bz,' ')<>'￥' and nvl(bm,' ')<>'积分兑换' and subbh='03711646') select nvl(a.jshj,0) 销售额,round(nvl(xsrw.rwje,0),2) 任务额,case when nvl(xsrw.rwje,0)<>0 then round(a.jshj*100/xsrw.rwje,2) else 0 end ||'%' 销售进度, nvl(a.mle,0) 毛利额,case when nvl(mlrw.rwje,0)<>0 then round(a.mle*100/mlrw.rwje,2) else 0 end||'%'  毛利进度 from (select sum(jshj) jshj,sum(case when b.hh is not null and a.ml<0 and a.sl>0 then 0 else a.ml end) mle from dtxs a  left join c_zkmdbmlpzmx b on a.hedgehh=b.hh  left join (select * from zk_ypfl where lb='自营品种') c on a.hedgehh=c.hh) a left join (select * from vw_zkzygxrw  where subbh='03711646' and pzlb='绩效销售额任务') xsrw on 1=1 left join (select * from vw_zkzygxrw  where subbh='" + map.get("departCode").toString() + "' and pzlb='月毛利额任务') mlrw on 1=1";
                query = sqlUtils.sendSql(sql2, "query");
                break;
            case 5:
                String sql5 = "select round(sum(c.jshj)/10000,2) jshj,round(sum(c.ml)/10000,2) ml,case when nvl(sum(b.jxrw),0)<>0 then round(sum(c.jshj)*100/sum(b.jxrw),2) else 0 end xswcl,\n" +
                        "case when nvl(sum(b.mlrw),0)<>0 then round(sum(nvl(c.ml,0)+nvl(d.ml,0))*100/sum(b.mlrw),2) else 0 end mlwcl \n" +
                        "from gl_custom a left join organization f on substr(a.tjbh,1,4)=f.code \n" +
                        "left join (select a.subbh,a.yxse*(b.xsxs/100) jxrw,a.ymle*(d.xsxs/100) mlrw from (select * from c_mdjyys where nd=to_char(sysdate,'yyyy') and yf=to_char(sysdate,'mm')) a \n" +
                        "left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join  C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid where a.rwlb='绩效销售额任务' and trunc(b.ksrq)=trunc(sysdate)) b on 1=1 \n" +
                        "left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join  C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid where a.rwlb='月毛利额任务' and trunc(b.ksrq)=trunc(sysdate)) d on 1=1) b on a.tjbh=b.subbh \n" +
                        "left join (select subbh,sum(jshj) jshj,sum(ml) ml from subfhd where kdrq=trunc(sysdate) and nvl(bz,' ')<>'会员礼品成本调整单' and nvl(bz,' ')<>'￥' \n" +
                        "group by subbh) c on a.tjbh=c.subbh \n" +
                        "left join (select subbh,abs(sum(ml)) ml from subfhd a left join c_zkmdbmlpzmx c on a.hedgehh=c.hh where kdrq=trunc(sysdate) \n" +
                        "and nvl(bz,' ')<>'会员礼品成本调整单' and nvl(bz,' ')<>'￥' and a.ml<0 and a.sl>0  and c.hh is not null AND substr(a.lsh,1,8)=a.subbh group by subbh) d on a.tjbh=d.subbh\n" +
                        "where (a.c_mdfq1='"+map.get("departName")+"' or a.C_DQ = '"+map.get("departName")+"')";
                query = sqlUtils.sendSql(sql5, "query");
                break;
            case 6:
                String sql6 = "";
                query = sqlUtils.sendSql(sql6, "query");

                break;
            case 7:
                String sql7 = "select round(sum(c.jshj)/10000,2) jshj,round(sum(c.ml)/10000,2) ml,case when nvl(sum(b.jxrw),0)<>0 then round(sum(c.jshj)*100/sum(b.jxrw),2) else 0 end xswcl,\n" +
                        "case when nvl(sum(b.mlrw),0)<>0 then round(sum(nvl(c.ml,0)+nvl(d.ml,0))*100/sum(b.mlrw),2) else 0 end mlwcl \n" +
                        "from gl_custom a left join organization f on substr(a.tjbh,1,4)=f.code \n" +
                        "left join (select a.subbh,a.yxse*(b.xsxs/100) jxrw,a.ymle*(d.xsxs/100) mlrw from (select * from c_mdjyys where nd=to_char(sysdate,'yyyy') and yf=to_char(sysdate,'mm')) a \n" +
                        "left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join  C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid where a.rwlb='绩效销售额任务' and trunc(b.ksrq)=trunc(sysdate)) b on 1=1 \n" +
                        "left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join  C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid where a.rwlb='月毛利额任务' and trunc(b.ksrq)=trunc(sysdate)) d on 1=1) b on a.tjbh=b.subbh \n" +
                        "left join (select subbh,sum(jshj) jshj,sum(ml) ml from subfhd where kdrq=trunc(sysdate) and nvl(bz,' ')<>'会员礼品成本调整单' and nvl(bz,' ')<>'￥' \n" +
                        "group by subbh) c on a.tjbh=c.subbh \n" +
                        "left join (select subbh,abs(sum(ml)) ml from subfhd a left join c_zkmdbmlpzmx c on a.hedgehh=c.hh where kdrq=trunc(sysdate) \n" +
                        "and nvl(bz,' ')<>'会员礼品成本调整单' and nvl(bz,' ')<>'￥' and a.ml<0 and a.sl>0  and c.hh is not null AND substr(a.lsh,1,8)=a.subbh group by subbh) d on a.tjbh=d.subbh \n" +
                        "where a.C_DQ = '"+map.get("departName")+"'";
                query = sqlUtils.sendSql(sql7, "query");
                break;
            case 9:
                String sql9 = "with  aa as (select a.subbh,a.jshj,a.ml,a.sl,a.hedgehh,a.lsh from subfhd a left join gl_custom c on c.tjbh=a.subbh \n" +
                        "where a.kdrq=trunc(sysdate) and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and c.c_mdfq = '"+map.get("departName")+"') \n" +
                        "select round(sum(a.jshj)/10000,1)  销售额,round(sum(a.ml)/10000,1)  毛利,case when nvl(sum(c.jxrw),0)<>0 then round(sum(a.jshj)*100/sum(c.jxrw),2) else 0 end 销售完成率 ,\n" +
                        "case when nvl(sum(c.mlrw),0)<>0 then round(sum(nvl(a.ml,0)+nvl(b.ml,0))*100/sum(c.mlrw),2) else 0 end 毛利完成率 from \n" +
                        "(select sum(jshj) jshj,sum(ml) ml from aa ) a \n" +
                        " left join (select abs(sum(ml)) ml from aa a left join c_zkmdbmlpzmx c on a.hedgehh=c.hh where a.ml<0 and a.sl>0 and c.hh is not null AND substr(a.lsh,1,8)=a.subbh) b on 1=1 \n" +
                        " left join (select sum(a.yxse*(b.xsxs/100)) jxrw,sum(a.ymle*(d.xsxs/100)) mlrw \n" +
                        "from (select * from c_mdjyys where nd=to_char(sysdate,'yyyy') and yf=to_char(sysdate,'mm')) a \n" +
                        "left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid where a.rwlb='绩效销售额任务' and trunc(b.ksrq)=trunc(sysdate)) b on 1=1 \n" +
                        "left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid where a.rwlb='月毛利额任务' and trunc(b.ksrq)=trunc(sysdate)) d on 1=1 \n" +
                        "left join gl_custom e on e.tjbh=a.subbh where e.c_mdfq='"+map.get("departName")+"') c on 1=1 ";
                query = sqlUtils.sendSql(sql9, "query");
                break;
            case 11:
                JSONObject jsonObject = dataBoardMapper.queryDayFour();
                JSONObject jsonObject1 = dataBoardMapper.queryMonthKL();
                jsonObject.put("当月客流",jsonObject1.getString("当月客流"));
                String sql= "select sum(a.yxse*(b.xsxs/100)) jxrw \n" +
                        "from (select * from c_mdjyys where nd=to_char(sysdate,'yyyy') and yf=to_char(sysdate,'mm')) a  \n" +
                        "left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join  C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid \n" +
                        "where a.rwlb='绩效销售额任务' and trunc(b.ksrq)=trunc(sysdate)) b on 1=1";
                String query1 = sqlUtils.sendSql(sql, "query");
                sql = "select sum(a.ymle*(b.xsxs/100)) jxrw \n" +
                        "from (select * from c_mdjyys where nd=to_char(sysdate,'yyyy') and yf=to_char(sysdate,'mm')) a  \n" +
                        "left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join  C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid \n" +
                        "where a.rwlb='月毛利额任务' and trunc(b.ksrq)=trunc(sysdate)) b on 1=1";
                String query2 = sqlUtils.sendSql(sql, "query");
                Double xse = jsonObject.getDouble("销售额");
                Double mle = jsonObject.getDouble("毛利额");
                DecimalFormat decimalFormat = new DecimalFormat("#.##");
                jsonObject.put("销售完成率",decimalFormat.format(xse/JSON.parseArray(query1).getJSONObject(0).getDouble("JXRW") * 100));
                jsonObject.put("毛利完成率",decimalFormat.format(mle/JSON.parseArray(query2).getJSONObject(0).getDouble("JXRW") * 100));
                return jsonObject;
            default:
                break;
        }
        return JSON.parseArray(query).getJSONObject(0);
    }
}
